package com.googlecode.garbagecan.dbtest.derby;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.apache.commons.beanutils.BasicDynaClass;
import org.apache.commons.beanutils.DynaBean;
import org.apache.commons.beanutils.DynaProperty;
import org.apache.commons.beanutils.ResultSetDynaClass;
import org.apache.commons.beanutils.RowSetDynaClass;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.lang.StringUtils;

public class TransactionTest {

	public static void main(String[] args) throws Exception {
//		findById();
//		list();
//		listDynaBean();
//		importData();
	}
	
	private static void findById() throws Exception {
		Connection conn = null;
		
		try {
			conn = getConnection();
			ResultSetHandler<Map<String, Object>> h = new ResultSetHandler<Map<String, Object>>() {
				public Map<String, Object> handle(ResultSet rs) throws SQLException {
					if (!rs.next()) {
						return null;
					}

					ResultSetMetaData rsmd = rs.getMetaData();
					int cols = rsmd.getColumnCount();
					Map<String, Object> result = new LinkedHashMap<String, Object>();

					for (int i = 1; i <= cols; i++) {
						result.put(rsmd.getColumnName(i), rs.getObject(i));
					}

					return result;
				}
			};

			QueryRunner run = new QueryRunner();
			Map<String, Object> result = run.query(conn, "SELECT * FROM TRANSACTIONS WHERE TRANSACTIONSID=?", h, 1);
			for (String key : result.keySet()) {
				System.out.println(key + ": " + result.get(key));
			}
		} finally {
			DbUtils.close(conn);
		}
	}
	
	private static void list() throws Exception {
		Connection conn = null;
		try {
			conn = getConnection();
			ResultSetHandler<List<Map<String, Object>>> h = new ResultSetHandler<List<Map<String, Object>>>() {
				public List<Map<String, Object>> handle(ResultSet rs) throws SQLException {
					List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
					while (rs.next()) {
						ResultSetMetaData rsmd = rs.getMetaData();
						int cols = rsmd.getColumnCount();
						Map<String, Object> map = new LinkedHashMap<String, Object>();
						for (int i = 1; i <= cols; i++) {
							map.put(rsmd.getColumnName(i), rs.getObject(i));
						}
						list.add(map);
					}
					return list;
				}
			};
		
			QueryRunner run = new QueryRunner();
			List<Map<String, Object>> list = run.query(conn, "SELECT * FROM TRANSACTIONS", h);
			for (Map<String, Object> map : list) {
				System.out.println("========================================");
				for (String key : map.keySet()) {
					System.out.println(key + ": " + map.get(key));
				}
			}
		} finally {
			DbUtils.close(conn);
		}
	}
	
	private static void listDynaBean() throws Exception {
		Connection conn = null;
		
		try {
			conn = getConnection();

			ResultSetHandler<List<DynaBean>> h = new ResultSetHandler<List<DynaBean>>() {
				public List<DynaBean> handle(ResultSet rs) throws SQLException {
					RowSetDynaClass rsdc = new RowSetDynaClass(rs);
					List<DynaBean> list = rsdc.getRows();
					return list;
				}
			};

			QueryRunner run = new QueryRunner();
			List<DynaBean> list = run.query(conn, "SELECT * FROM TRANSACTIONS", h);
			for (DynaBean dynaBean : list) {
				System.out.println("========================================");
				System.out.println(dynaBean.get("entityid"));
				System.out.println(dynaBean.get("entityname"));
			}
		} finally {
			DbUtils.closeQuietly(conn);
		}
	}
	
	private static void importData() throws Exception {
		Connection conn = null;
		
		try {
			conn = getConnection();
			QueryRunner run = new QueryRunner();
			
			String sql = "INSERT INTO TRANSACTIONS\n" +
					"(ENTITYID, ENTITYNAME, TXID, TXENTITYID, TXENTITYNAME, TXREQUEST, TXTIMESTAMP, TXDATE, TXTIME, TXHASHCODE, PARENTTXHASHCODE, SYNCACTION, USERID, MEMO, STATUS, RESPONSEMESSAGE)\n" +
					"VALUES('ENTITYID_0', 'ENTITYNAME_0', 'TXID_0', 'TXENTITYID_0', 'TXENTITYNAME_0', 'TXREQUEST_0', '2014-05-01 00:00:00', '2014-05-01', '00:00:00', 'TXHASHCODE_0', 'PARENTTXHASHCODE_0', 'SYNCACTION_0', 'JJIANG', 'MEMO_0', 'STATUS_0', 'RESPONSEMESSAGE_0')";
			for (int i = 0; i < 25; i++) {
				run.update(conn, StringUtils.replace(sql, "_0", "_"+i));
			}
			conn.commit();
		} finally {
			DbUtils.closeQuietly(conn);
		}
	}
	
	private static Connection getConnection() throws Exception {
		Class.forName("org.apache.derby.jdbc.ClientDriver");
		String url = "jdbc:derby://localhost:1527/myapp";
		Connection conn = DriverManager.getConnection(url);
		conn.setAutoCommit(false);
		return conn;
	}
}
